/*
 Oracle 21c Compatible Script
 Schema: CT
*/

-- 确保序列存在
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM all_sequences WHERE sequence_owner = 'CT' AND sequence_name = 'SEQ_T_USER';
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE CT.seq_t_user START WITH 17 INCREMENT BY 1 NOCACHE NOCYCLE';
  END IF;
END;
/

-- ----------------------------
-- Table structure for T_USER
-- ----------------------------


CREATE TABLE CT.T_USER (
  "USERID" NUMBER NOT NULL,
  "USERNAME" VARCHAR2(254 BYTE) NOT NULL,
  "USERPASSWORD" VARCHAR2(254 BYTE) NOT NULL,
  "ISADMIN" NUMBER NOT NULL
);
/*LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
;*/

-- ----------------------------
-- Records of T_USER
-- ----------------------------
INSERT INTO CT.T_USER VALUES ('1', 'admin', 'admin', '1');
INSERT INTO CT.T_USER VALUES ('2', '李明', '123456', '0');
INSERT INTO CT.T_USER VALUES ('11', 'zhang', '123', '0');
INSERT INTO CT.T_USER VALUES ('13', 'zhao', 'abc', '1');
INSERT INTO CT.T_USER VALUES ('14', 'wangpeng', '123456', '0');
INSERT INTO CT.T_USER VALUES ('15', 'wp123', 'wp456', '1');
INSERT INTO CT.T_USER VALUES ('16', 'wp123', 'wp456', '1');
INSERT INTO CT.T_USER VALUES ('17', 'wp1235', 'wp4567', '1');

-- ----------------------------
-- Primary Key structure for table T_USER
-- ----------------------------
ALTER TABLE CT.T_USER ADD CONSTRAINT "SYS_C0011108" PRIMARY KEY ("USERID");

-- ----------------------------
-- Checks structure for table T_USER
-- ----------------------------
ALTER TABLE CT.T_USER ADD CONSTRAINT "SYS_C0011105" CHECK ("USERNAME" IS NOT NULL) ;
ALTER TABLE CT.T_USER ADD CONSTRAINT "SYS_C0011106" CHECK ("USERPASSWORD" IS NOT NULL) ;
ALTER TABLE CT.T_USER ADD CONSTRAINT "SYS_C0011107" CHECK ("ISADMIN" IS NOT NULL) ;

-- ----------------------------
-- Triggers structure for table T_USER
-- ----------------------------
CREATE OR REPLACE TRIGGER CT.t_user_trig 
BEFORE INSERT ON CT.T_USER 
FOR EACH ROW
BEGIN
    SELECT seq_t_user.nextval INTO :new.userid FROM dual;
END;
/




CREATE TABLE "SYSTEM"."T_USER" (
                                   "USERID" NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 17) PRIMARY KEY, -- 初始值从17开始，避免与现有数据冲突
                                   "USERNAME" VARCHAR2(254 CHAR) NOT NULL,       -- 改用CHAR语义
                                   "USERPASSWORD" VARCHAR2(254 CHAR) NOT NULL,
                                   "ISADMIN" NUMBER NOT NULL
)
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (BUFFER_POOL DEFAULT)
DISABLE ROW MOVEMENT;

-- 插入数据（移除显式USERID，依赖IDENTITY列）
INSERT INTO "SYSTEM"."T_USER" ("USERNAME", "USERPASSWORD", "ISADMIN") VALUES ('admin', 'admin', 1);
INSERT INTO "SYSTEM"."T_USER" ("USERNAME", "USERPASSWORD", "ISADMIN") VALUES ('李明', '123456', 0);